﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace ITMAN.Klassen
{
    class CL_assetman
    {
        // private Methoden
        private SqlConnection get_DB_connection()
        {
            SqlConnection connector = new SqlConnection();
            connector.ConnectionString = "Data Source=" + config.Default.SQLserver + ";" +
                                            "Initial Catalog= " + config.Default.SQLdatabase + ";" +
                                            "User ID=" + config.Default.SQLusername + ";" +
                                            "Password=" + config.Default.SQLpw + "";
            return connector;
        }
        // Assets behandeln
        // Spaltennamen der Tabelle TBL_asset auslesen
        public List<string> get_asset_properties()
        {
            List<string> result = new List<string>();
            try
            {
                string sql_querey = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'TBL_assets' AND TABLE_SCHEMA='dbo'";
                SqlConnection connector = get_DB_connection();
                SqlCommand command = new SqlCommand(sql_querey, connector);
                SqlDataReader reader;
                connector.Open();
                reader = command.ExecuteReader();
                while (reader.Read())
                {
                    result.Add(reader[0].ToString());
                }
                connector.Close();

            }
            catch (SystemException ex)
            {
                MessageBox.Show(ex.Message);
            }
            return result;
        }

        public List<string> get_mdt_properties()
        {
            List<string> result = new List<string>();
            try
            {
                string sql_querey = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'TBL_mdt_details' AND TABLE_SCHEMA='dbo'";
                SqlConnection connector = get_DB_connection();
                SqlCommand command = new SqlCommand(sql_querey, connector);
                SqlDataReader reader;
                connector.Open();
                reader = command.ExecuteReader();
                while (reader.Read())
                {
                    result.Add(reader[0].ToString());
                }
                connector.Close();

            }
            catch (SystemException ex)
            {
                MessageBox.Show(ex.Message);
            }
            return result;
        }

        public List<string> get_asset_details_list(string asset_nr)
        {
            List<string> lst = new List<string>();
            try
            {
                string sql_querey = "SELECT * FROM [" + config.Default.SQLdatabase + "].[dbo].[TBL_inventar] WHERE [Ident_nr] like '" + asset_nr + "'";
                SqlConnection connector = get_DB_connection();
                SqlCommand command = new SqlCommand(sql_querey, connector);
                connector.Open();
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    int counter = reader.FieldCount;
                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        lst.Add(reader[i].ToString());
                    }
                }
                connector.Close();

            }
            catch (SystemException ex)
            {
                MessageBox.Show(ex.Message);
            }
            return lst;
        }

        public DataTable get_assets_to_datatable(string kategorie)
        {
            DataTable result = new DataTable();
            try
            {
                string sql_querey = "SELECT * FROM [" + config.Default.SQLdatabase + "].[dbo].[TBL_assets] WHERE [Kategorie] like '" + kategorie + "'";
                SqlConnection connector = get_DB_connection();
                SqlCommand command = new SqlCommand(sql_querey, connector);
                connector.Open();
                result.Load(command.ExecuteReader());
                connector.Close();

            }
            catch (SystemException ex)
            {
                MessageBox.Show(ex.Message);
            }
            return result;
        }

        public DataTable get_assets_to_datatable()
        {
            DataTable result = new DataTable();
            try
            {
                string sql_querey = "SELECT * FROM [" + config.Default.SQLdatabase + "].[dbo].[TBL_assets]";
                SqlConnection connector = get_DB_connection();
                SqlCommand command = new SqlCommand(sql_querey, connector);
                connector.Open();
                result.Load(command.ExecuteReader());
                connector.Close();

            }
            catch (SystemException ex)
            {
                MessageBox.Show(ex.Message);
            }
            return result;
        }

        public DataTable search_assets(string searchstring)
        {
            DataTable result = new DataTable();
            try
            {
                string sql_querey = "SELECT * FROM [" + config.Default.SQLdatabase + "].[dbo].[TBL_assets] ";
                sql_querey += "WHERE [ident_nr] like '" + searchstring + "'";
                sql_querey += "OR [Seriennummer] like '" + searchstring + "'";
                sql_querey += "OR [Bezeichnung] like '" + searchstring + "'";

                SqlConnection connector = get_DB_connection();
                SqlCommand command = new SqlCommand(sql_querey, connector);
                connector.Open();
                result.Load(command.ExecuteReader());
                connector.Close();

            }
            catch (SystemException ex)
            {
                MessageBox.Show(ex.Message);
            }
            return result;
        }

        public DataTable get_assets_detail_to_datatable(string asset_nr)
        {
            DataTable result = new DataTable();
            try
            {
                string sql_querey = "SELECT * FROM [" + config.Default.SQLdatabase + "].[dbo].[TBL_assets] WHERE [asset_nr] like '" + asset_nr + "'";
                SqlConnection connector = get_DB_connection();
                SqlCommand command = new SqlCommand(sql_querey, connector);
                connector.Open();
                result.Load(command.ExecuteReader());
                connector.Close();

            }
            catch (SystemException ex)
            {
                MessageBox.Show(ex.Message);
            }
            return result;
        }

        public DataTable get_asset_histroy(string asset_nr)
        {
            DataTable result = new DataTable();
            try
            {
                string sql_querey = "SELECT * FROM [" + config.Default.SQLdatabase + "].[dbo].[TBL_del_assets] WHERE [asset_nr] like '" + asset_nr + "'";
                SqlConnection connector = get_DB_connection();
                SqlCommand command = new SqlCommand(sql_querey, connector);
                connector.Open();
                result.Load(command.ExecuteReader());
                connector.Close();

            }
            catch (SystemException ex)
            {
                MessageBox.Show(ex.Message);
            }
            return result;
        }

        public void insert_new_asset(DataGridViewRow DGV_row)
        {
            int counter = DGV_row.Cells.Count;
            string sql_insert = "INSERT INTO [" + config.Default.SQLdatabase + "].[dbo].[TBL_assets] ([";

            for (int i = 1; i < counter; i++)
            {
                sql_insert += DGV_row.Cells[i].OwningColumn.Name;
                if (i < counter-1)
                {
                    sql_insert += "],[";
                }
                else
                {
                    sql_insert += "])";
                }
            }
            sql_insert += "VALUES (";

            for (int ii = 1; ii < counter; ii++)
            {
                switch (DGV_row.Cells[ii].OwningColumn.Name)
                {
                    case "Ersteller":
                        sql_insert += "'";
                        sql_insert += LoginInformations.username;
                        sql_insert += "'";
                        break;
                    case "Erstelldatum":
                        sql_insert += "GETDATE()";
                        break;
                    default:
                        sql_insert += "'";
                        sql_insert += DGV_row.Cells[ii].Value;
                        sql_insert += "'";
                        break;
                }
                if (ii < counter - 1)
                {
                    sql_insert += ",";
                }
                else
                {
                    sql_insert += ")";
                }

            }
            SqlConnection connector = get_DB_connection();
            SqlCommand command = new SqlCommand(sql_insert, connector);
            connector.Open();
            command.ExecuteNonQuery();
            connector.Close();
            //string sql_insert = "INSERT INTO [" + config.Default.SQLdatabase + "].[dbo].[TBL_inventar] ([Ident_nr],[KN_inventar],[SN_nummer],[typ],[modell],[bezeichnung],[standort],[kaufdatum],[ende_garantie],[status],[kommentar])" +
            //        "VALUES ('" + ident_nr + "','" + KN_ident + "','" + seriennummer + "','" + typ + "','" + modell + "','" + bezeichnung + "','" + standort + "','" + kauf + "','" + garantie + "','" + status + "','" + kommentar + "');";
        }

        // Asset updaten mit dem Status "gelöscht" danach löschen des Asset
        public void delete_asset(string asset_nr, DataGridViewRow DGV_row)
        {
            try
            {
                for (int i = 1; i < DGV_row.Cells.Count; i++)
                {
                    if (DGV_row.Cells[i].OwningColumn.Name == "Status")
                    {
                        DGV_row.Cells[i].Value = "gelöscht";
                    }
                }
                if (update_asset(asset_nr, DGV_row))
                {
                    string sql_delete = "DELETE FROM [" + config.Default.SQLdatabase + "].[dbo].[TBL_assets]" +
                        "OUTPUT deleted.* " +
                        "INTO [" + config.Default.SQLdatabase + "].[dbo].[TBL_del_assets]" +
                        "WHERE [asset_nr] like '" + asset_nr + "';";
                    SqlConnection connector = get_DB_connection();
                    SqlCommand command_delete = new SqlCommand(sql_delete, connector);
                    connector.Open();
                    command_delete.ExecuteNonQuery();
                    connector.Close();
                }
            }
            catch (SystemException ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        // Asset bearbeiten
        public bool update_asset(string asset_nr, DataGridViewRow DGV_row)
        {
            bool result = false;
            try
            {
                string sql_update = "UPDATE [" + config.Default.SQLdatabase + "].[dbo].[TBL_assets]";
                sql_update += "SET ";

                for (int i = 1; i < DGV_row.Cells.Count; i++)
                {
                    sql_update += DGV_row.Cells[i].OwningColumn.Name +"=";
                    switch (DGV_row.Cells[i].OwningColumn.Name)
                    {
                        case "geändert_von": 
                            sql_update += "'";
                            sql_update += LoginInformations.username;
                            sql_update += "'";
                            break;
                        case "letzte_änderung":
                            sql_update += "GETDATE()";
                            break;
                        default:
                            sql_update += "'";
                            sql_update += DGV_row.Cells[i].Value;
                            sql_update += "'";
                            break;
                    }
                    if (i < DGV_row.Cells.Count - 1)
                    {
                        sql_update += ",";
                    }
                }
                sql_update += "OUTPUT deleted.* ";
                sql_update += "INTO [" + config.Default.SQLdatabase + "].[dbo].[TBL_del_assets]";
                sql_update += "WHERE [asset_nr] like '" + asset_nr + "';";
                SqlConnection connector = get_DB_connection();
                SqlCommand command = new SqlCommand(sql_update, connector);
                connector.Open();
                int affected_row = command.ExecuteNonQuery();
                connector.Close();
                if (affected_row > 0)
                {
                    result = true;
                }
            }
            catch (SystemException ex)
            {
                MessageBox.Show(ex.Message);
            }
            return result;
        }

        // Wartung behandeln
        public DataTable get_wartung_to_datatable(string ident_nr)
        {
            DataTable result = new DataTable();
            try
            {
                string sql_querey = "SELECT * FROM [" + config.Default.SQLdatabase + "].[dbo].[TBL_wartung] WHERE Ident_nr like '" + ident_nr + "'";
                SqlConnection connector = get_DB_connection();
                SqlCommand command = new SqlCommand(sql_querey, connector);
                connector.Open();
                result.Load(command.ExecuteReader());
                connector.Close();

            }
            catch (SystemException ex)
            {
                MessageBox.Show(ex.Message);
            }
            return result;
        }

        public int insert_new_wartung(int wartungs_id, string ident_nr, string wartung, string kommentar)
        {
            int result = 0;
            try
            {
                string sql_insert = "INSERT INTO [" + config.Default.SQLdatabase + "].[dbo].[TBL_wartung] ([Ident_nr],[wartung],[kommentar])" +
                                    "VALUES ('" + ident_nr + "','" + wartung + "','" + kommentar + "');";
                SqlConnection connector = get_DB_connection();
                SqlCommand command = new SqlCommand(sql_insert, connector);
                connector.Open();
                command.ExecuteNonQuery();
                result = 1;
                connector.Close();
            }
            catch (SystemException ex)
            {
                MessageBox.Show(ex.Message);
            }
            return result;
        }

        // Standorte
        public List<string> get_standorte()
        {
            List<string> lst = new List<string>();
            try
            {
                string sql_querey = "SELECT Standort FROM [" + config.Default.SQLdatabase + "].[dbo].[TBL_standort]";
                SqlConnection connector = get_DB_connection();
                SqlCommand command = new SqlCommand(sql_querey, connector);
                connector.Open();
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    lst.Add(reader[0].ToString());
                }
                connector.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            return lst;
        }

        // Items Status update
        public void update_status(string ident_nr, string status)
        {
            try
            {
                string sql_update = "UPDATE [" + config.Default.SQLdatabase + "].[dbo].[TBL_inventar]" +
                    "SET [status] = '" + status + "'" +
                    "WHERE [ident_nr] like '" + ident_nr + "';";
                SqlConnection connector = get_DB_connection();
                SqlCommand command = new SqlCommand(sql_update, connector);
                connector.Open();
                command.ExecuteNonQuery();
                connector.Close();
            }
            catch (SystemException ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        // Kategorien laden
        public List<string> get_kategorien()
        {
            List<string> lst = new List<string>();
            try
            {
                string sql_querey = "SELECT kategorie FROM [" + config.Default.SQLdatabase + "].[dbo].[TBL_kategorie]";
                SqlConnection connector = get_DB_connection();
                SqlCommand command = new SqlCommand(sql_querey, connector);
                connector.Open();
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    lst.Add(reader[0].ToString());
                }
                connector.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            return lst;
        }

        public List<string> get_status()
        {
            List<string> lst = new List<string>();
            try
            {
                string sql_querey = "SELECT Status FROM [" + config.Default.SQLdatabase + "].[dbo].[TBL_status]";
                SqlConnection connector = get_DB_connection();
                SqlCommand command = new SqlCommand(sql_querey, connector);
                connector.Open();
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    lst.Add(reader[0].ToString());
                }
                connector.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            return lst;

        }

        // MDT
        public DataTable get_mdt_detail_to_datatable(string asset_nr)
        {
            DataTable result = new DataTable();
            try
            {
                string sql_querey = "SELECT * FROM [" + config.Default.SQLdatabase + "].[dbo].[TBL_mdt_details] WHERE [asset_nr] like '" + asset_nr + "'";
                SqlConnection connector = get_DB_connection();
                SqlCommand command = new SqlCommand(sql_querey, connector);
                connector.Open();
                result.Load(command.ExecuteReader());
                connector.Close();

            }
            catch (SystemException ex)
            {
                MessageBox.Show(ex.Message);
            }
            return result;
        }

        public void insert_new_mdt(DataGridViewRow DGV_row,string asset_nr)
        {
            int counter = DGV_row.Cells.Count;
            string sql_insert = "INSERT INTO [" + config.Default.SQLdatabase + "].[dbo].[TBL_mdt_details] ([";

            for (int i = 0; i < counter; i++)
            {
                sql_insert += DGV_row.Cells[i].OwningColumn.Name;
                if (i < counter - 1)
                {
                    sql_insert += "],[";
                }
                else
                {
                    sql_insert += "])";
                }
            }
            sql_insert += "VALUES (";

            for (int ii = 0; ii < counter; ii++)
            {
                switch (DGV_row.Cells[ii].OwningColumn.Name)
                {
                    case "Ersteller":
                        sql_insert += "'";
                        sql_insert += LoginInformations.username;
                        sql_insert += "'";
                        break;
                    case "Erstelldatum":
                        sql_insert += "GETDATE()";
                        break;
                    case "asset_nr":
                        sql_insert += "'";
                        sql_insert += asset_nr;
                        sql_insert += "'";
                        break;
                    default:
                        sql_insert += "'";
                        sql_insert += DGV_row.Cells[ii].Value;
                        sql_insert += "'";
                        break;
                }
                if (ii < counter - 1)
                {
                    sql_insert += ",";
                }
                else
                {
                    sql_insert += ")";
                }

            }
            SqlConnection connector = get_DB_connection();
            SqlCommand command = new SqlCommand(sql_insert, connector);
            connector.Open();
            command.ExecuteNonQuery();
            connector.Close();
        }

        public bool update_mdt_details(string asset_nr, DataGridViewRow DGV_row)
        {
            bool result = false;
            try
            {
                string sql_update = "UPDATE [" + config.Default.SQLdatabase + "].[dbo].[TBL_mdt_details]";
                sql_update += "SET ";

                for (int i = 1; i < DGV_row.Cells.Count; i++)
                {
                    sql_update += "[";
                    sql_update += DGV_row.Cells[i].OwningColumn.Name + "]=";
                    switch (DGV_row.Cells[i].OwningColumn.Name)
                    {
                        case "geändert_von":
                            sql_update += "'";
                            sql_update += LoginInformations.username;
                            sql_update += "'";
                            break;
                        case "letzte_änderung":
                            sql_update += "GETDATE()";
                            break;
                        default:
                            sql_update += "'";
                            sql_update += DGV_row.Cells[i].Value;
                            sql_update += "'";
                            break;
                    }
                    if (i < DGV_row.Cells.Count - 1)
                    {
                        sql_update += ",";
                    }
                }
                sql_update += "OUTPUT deleted.* ";
                sql_update += "INTO [" + config.Default.SQLdatabase + "].[dbo].[TBL_del_mdt_details]";
                sql_update += "WHERE [asset_nr] like '" + asset_nr + "';";
                SqlConnection connector = get_DB_connection();
                SqlCommand command = new SqlCommand(sql_update, connector);
                connector.Open();
                int affected_row = command.ExecuteNonQuery();
                connector.Close();
                if (affected_row > 0)
                {
                    result = true;
                }
            }
            catch (SystemException ex)
            {
                MessageBox.Show(ex.Message);
            }
            return result;
        }

        // Wartung

        public DataTable get_asset_wartung_datatable(string asset_nr)
        {
            DataTable result = new DataTable();
            try
            {
                string sql_querey = "SELECT * FROM [" + config.Default.SQLdatabase + "].[dbo].[TBL_asset_wartung] WHERE [asset_nr] like '" + asset_nr + "'";
                SqlConnection connector = get_DB_connection();
                SqlCommand command = new SqlCommand(sql_querey, connector);
                connector.Open();
                result.Load(command.ExecuteReader());
                connector.Close();
            }
            catch (SystemException ex)
            {
                MessageBox.Show(ex.Message);
            }
            return result;
        }

        public List<string> get_asset_wartung_properties()
        {
            List<string> result = new List<string>();
            try
            {
                string sql_querey = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'TBL_asset_wartung' AND TABLE_SCHEMA='dbo'";
                SqlConnection connector = get_DB_connection();
                SqlCommand command = new SqlCommand(sql_querey, connector);
                SqlDataReader reader;
                connector.Open();
                reader = command.ExecuteReader();
                while (reader.Read())
                {
                    result.Add(reader[0].ToString());
                }
                connector.Close();

            }
            catch (SystemException ex)
            {
                MessageBox.Show(ex.Message);
            }
            return result;
        }

        public List<string> get_wartung_fehlercode()
        {
            List<string> lst = new List<string>();
            try
            {
                string sql_querey = "SELECT fehler_code FROM [" + config.Default.SQLdatabase + "].[dbo].[TBL_wartung_fehlercode]";
                SqlConnection connector = get_DB_connection();
                SqlCommand command = new SqlCommand(sql_querey, connector);
                connector.Open();
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    lst.Add(reader[0].ToString());
                }
                connector.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            return lst;
        }

        public void insert_new_asset_wartung(DataGridViewRow DGV_row)
        {
            int counter = DGV_row.Cells.Count;
            string sql_insert = "INSERT INTO [" + config.Default.SQLdatabase + "].[dbo].[TBL_asset_wartung] ([";

            for (int i = 1; i < counter; i++)
            {
                sql_insert += DGV_row.Cells[i].OwningColumn.Name;
                if (i < counter - 1)
                {
                    sql_insert += "],[";
                }
                else
                {
                    sql_insert += "])";
                }
            }
            sql_insert += "VALUES (";

            for (int ii = 1; ii < counter; ii++)
            {
                switch (DGV_row.Cells[ii].OwningColumn.Name)
                {
                    case "Ersteller":
                        sql_insert += "'";
                        sql_insert += LoginInformations.username;
                        sql_insert += "'";
                        break;
                    case "Erstelldatum":
                        sql_insert += "GETDATE()";
                        break;
                    default:
                        sql_insert += "'";
                        sql_insert += DGV_row.Cells[ii].Value;
                        sql_insert += "'";
                        break;
                }
                if (ii < counter - 1)
                {
                    sql_insert += ",";
                }
                else
                {
                    sql_insert += ")";
                }

            }
            SqlConnection connector = get_DB_connection();
            SqlCommand command = new SqlCommand(sql_insert, connector);
            connector.Open();
            command.ExecuteNonQuery();
            connector.Close();
            //string sql_insert = "INSERT INTO [" + config.Default.SQLdatabase + "].[dbo].[TBL_inventar] ([Ident_nr],[KN_inventar],[SN_nummer],[typ],[modell],[bezeichnung],[standort],[kaufdatum],[ende_garantie],[status],[kommentar])" +
            //        "VALUES ('" + ident_nr + "','" + KN_ident + "','" + seriennummer + "','" + typ + "','" + modell + "','" + bezeichnung + "','" + standort + "','" + kauf + "','" + garantie + "','" + status + "','" + kommentar + "');";
        }


        // Allgemeine Funktionen 

        private List<object> Datatable_to_lst (DataTable dtb)
        {
            List<object> lst = new List<object>();            



            return lst;


        }

    }
}
